package com.wind.trademark.task.dao.impl;


import java.util.Map;

import org.apache.commons.lang.StringUtils;

public class TaskSql {
	public static String queryTaskSQL( Map<String,Object> params){
		
		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("SELECT task.ID,task.COMP_ID,task.CN_NAME ,task.EN_NAME ,task.CATEGORY_ID,task.PRO_NAME,task.OWNER,task_status.NAME STATUS,task.CREATE_TIME,company.NAME compName,company.ID compId FROM TK_TASK task,TK_TASK_STATUS  task_status,CT_COMPANY company WHERE company.ID=task.COMP_ID AND task_status.CODE=task.STATUS ");

		if(StringUtils.isNotEmpty((String) params.get("companyName"))){
			stringBuffer.append("AND  company.NAME LIKE :companyName   ");		
		}
		if(StringUtils.isNotEmpty((String) params.get("taskName"))){
			stringBuffer.append("AND (task.CN_NAME LIKE :taskName OR task.EN_NAME LIKE :taskName ) ");		
		}
		if(StringUtils.isNotEmpty((String) params.get("status"))){
			stringBuffer.append("AND task.STATUS = :status ");		
		}
		if(StringUtils.isNotEmpty((String) params.get("beforeCreateTime"))){
			stringBuffer.append("AND task.CREATE_TIME > :beforeCreateTime ");		
		}
		if(StringUtils.isNotEmpty((String) params.get("afterCreateTime"))){
			stringBuffer.append("AND task.CREATE_TIME > :afterCreateTime ");		
		}
		if(StringUtils.isNotEmpty((String) params.get("owner"))){
			stringBuffer.append("AND task.OWNER =:owner ");		
		}
		stringBuffer.append("ORDER BY task.CREATE_TIME");
		return stringBuffer.toString();
	}

	public static String removeAllCategoryWithTask() {
		return "DELETE  FROM TK_TASK_CATEGORY  WHERE TASK_ID=:taskId";
	}

	public static String getL3CategoriesByTaskId() {
		StringBuffer stringBuffer= new StringBuffer();

		stringBuffer.append("SELECT ");
		stringBuffer.append("	bs_category.* ");
		stringBuffer.append("FROM ");
		stringBuffer.append("	bs_category bs_category, ");
		stringBuffer.append("	tk_task_category tk_task_category, ");
		stringBuffer.append("	tk_task tk_task ");
		stringBuffer.append("WHERE ");
		stringBuffer.append("	bs_category.ID = tk_task_category.CATEGORY_ID  ");
		stringBuffer.append("AND tk_task.ID = tk_task_category.TASK_ID  ");
		stringBuffer.append("AND tk_task.ID = :taskId ");
		
		return stringBuffer.toString();

	}
	

	
	public static String getTask4Bill() {
		StringBuffer stringBuffer= new StringBuffer();

		stringBuffer.append("SELECT ");
		stringBuffer.append("tk_task.CODE, ");
		stringBuffer.append("tk_task.CN_NAME, ");
		stringBuffer.append("tk_task.EN_NAME, ");
		stringBuffer.append("tk_task.CREATE_BY, ");
		stringBuffer.append("tk_task.CREATE_TIME,");
		stringBuffer.append("ct_company.CN_NAME COMPANY_CN_NAME, ");
		stringBuffer.append("ct_contactor.`NAME` CONTACTOR_NAME, ");
		stringBuffer.append("ct_contactor.FAX, ");
		stringBuffer.append("ct_contactor.MOBILE, ");
		stringBuffer.append("ct_contactor.EMAIL, ");
		stringBuffer.append("lawyer.ACCOUNT LAWYER_NAME,");
		stringBuffer.append("creater.ACCOUNT CREATER_NAME ");
		stringBuffer.append("FROM ");
		stringBuffer.append("   ct_company ct_company, ");
		stringBuffer.append("	tk_task tk_task, ");
		stringBuffer.append("	ct_contactor ct_contactor, ");
		stringBuffer.append("	acc_user lawyer, ");
		stringBuffer.append("	acc_user creater ");
		stringBuffer.append("WHERE ");
		stringBuffer.append("	ct_company.ID = tk_task.COMP_ID ");
		stringBuffer.append("AND tk_task.`OWNER` = lawyer.ID ");
		stringBuffer.append("AND creater.ID=tk_task.CREATE_BY ");
		stringBuffer.append("AND ct_company.ID = ct_contactor.COMP_ID ");
		stringBuffer.append("AND ct_contactor.ISDEFAULT = 'Y' ");
		stringBuffer.append("AND tk_task.ID = :taskId ");


		return stringBuffer.toString();

	}

	public static String goolgeTaskSQL() {
		String sql="SELECT tk_task.ID 'key', CONCAT(tk_task.CODE,'/',COALESCE(tk_task.CN_NAME, ''),'/',COALESCE(tk_task.EN_NAME, '')) value FROM tk_task tk_task WHERE ( tk_task.`CODE` LIKE :queryParam OR tk_task.CN_NAME LIKE :queryParam OR tk_task.EN_NAME LIKE :queryParam ) ORDER BY tk_task.CODE";
		return sql;
	}
	public static String getAllTaskSQL() {
		String sql="SELECT tk_task.ID 'key', CONCAT(tk_task.CODE,'/',COALESCE(tk_task.CN_NAME, ''),'/',COALESCE(tk_task.EN_NAME, '')) value FROM tk_task tk_task ORDER BY tk_task.CODE";
		return sql;
	}

	public static String goolgeTaskSQL(Map<String, Object> params) {
		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("SELECT   ");
		stringBuffer.append("	tk_task.ID 'key',   ");
		stringBuffer.append("	CONCAT(   ");
		stringBuffer.append("		tk_task.TASK_NAME,   ");
		stringBuffer.append("		'/',   ");
		stringBuffer.append("		COALESCE(tk_task.CN_NAME, ''),   ");
		stringBuffer.append("		'/',   ");
		stringBuffer.append("		COALESCE(tk_task.EN_NAME, '')   ");
		stringBuffer.append("	)   ");
		stringBuffer.append("VALUE   ");
		stringBuffer.append("   ");
		stringBuffer.append("FROM   ");
		stringBuffer.append("	tk_task tk_task   ");
		stringBuffer.append("WHERE 1=1   ");
		if(params.entrySet().size()==1&&StringUtils.isNotEmpty((String) params.get("key"))){
			stringBuffer.append("AND tk_task.ID=:key   ");
		}
		if(StringUtils.isNotEmpty((String) params.get("queryParam"))){
			stringBuffer.append("AND	(   ");
			stringBuffer.append("		tk_task.TASK_NAME LIKE :queryParam   ");
			stringBuffer.append("		OR tk_task.CN_NAME LIKE :queryParam   ");
			stringBuffer.append("		OR tk_task.EN_NAME LIKE :queryParam   ");
			stringBuffer.append("	)   ");
			
			params.put("queryParam", "%"+(String) params.get("queryParam")+"%");
		}
		stringBuffer.append("ORDER BY   ");
		stringBuffer.append("	tk_task.ID ");
		return stringBuffer.toString();
	}

	public static String getLogListByTaskId() {
		StringBuffer stringBuffer= new StringBuffer();

		stringBuffer.append("SELECT ");
		stringBuffer.append("tk_task_log.*, ");
 
		stringBuffer.append("creater.name CREATE_NAME ,");
		stringBuffer.append("updater.name LAST_UPDATE_NAME ");

		stringBuffer.append("FROM ");
 		stringBuffer.append("	tk_task_log tk_task_log, ");
		stringBuffer.append("	acc_user creater, ");
		stringBuffer.append("	acc_user updater ");
		stringBuffer.append("WHERE ");
  		stringBuffer.append(" creater.ID=tk_task_log.CREATE_BY ");
 		stringBuffer.append("AND updater.ID=tk_task_log.LAST_UPDATE_BY ");

		stringBuffer.append("AND tk_task_log.TASK_ID = :taskId ");
		return stringBuffer.toString();
		
	}

	public static String findNextStepsTaskStatus() {
		return "SELECT tk_task_status.* FROM tk_task_status tk_task_status WHERE tk_task_status.CODE IN (:nextSteps)";
	}
	
	public static String deleteTkImg() {
		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("DELETE ");
		stringBuffer.append("FROM tk_img  ");
		stringBuffer.append("WHERE id IN (:ids) ");
		return stringBuffer.toString();
	}
	
	public static String deleteTkAttachment() {
		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("DELETE ");
		stringBuffer.append("FROM tk_attachment  ");
		stringBuffer.append("WHERE id IN (:ids) ");
		return stringBuffer.toString();
	}
}
